package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;

import orm.CollectivityClass;
import orm.IndividualClass;
import orm.Student;
import orm.Teacher;
import util.DBHelper;

public class CollectivityClassDAO {
	
	
	
	public CollectivityClass getClassID(String classID) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		CollectivityClass collectivityClass = new CollectivityClass();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from collectivityclass where ClassID =" + classID + "");
		while (rs.next()) {
			int i = 1;
			collectivityClass.setClassID(rs.getInt(i++));
			collectivityClass.setTeacherID(rs.getString(i++));
			collectivityClass.setTeacherName(rs.getString(i++));
			collectivityClass.setEstablishDate(rs.getString(i++));
			collectivityClass.setClassTerm(rs.getString(i++));
			collectivityClass.setClassSort(rs.getInt(i++));
			collectivityClass.setClassAttribute(rs.getInt(i++));
			collectivityClass.setServiceType(rs.getInt(i++));
			collectivityClass.setClassName(rs.getString(i++));
			collectivityClass.setAssignment(rs.getInt(i++));
			collectivityClass.setLiability(rs.getInt(i++));
			
			
		}
		
		rs.close();
		stmt.close();
		conn.close();
		return collectivityClass;
	}
	
	public void insertCollectivityClass(CollectivityClass collectivityClass) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
	
		
		stmt = conn.createStatement();
		stmt.execute("insert into collectivityclass values(" + collectivityClass.getClassID() + ", '" +
				collectivityClass.getTeacherID() + "','" + collectivityClass.getTeacherName() + "', '" + 
				collectivityClass.getEstablishDate() + "', '" + collectivityClass.getClassTerm() + "', " + collectivityClass.getClassSort() + ", " + 
				collectivityClass.getClassAttribute() + ", " + collectivityClass.getServiceType() + ", '" +
				collectivityClass.getClassName() + "', " +
				collectivityClass.getAssignment() + ", " + collectivityClass.getLiability() + ")");
		
		stmt.close();
		conn.close();
	}
	
	public void updateCollectivityClass(Statement statement, CollectivityClass collectivityClass) throws SQLException {
		statement.execute("update collectivityclass set ClassID =" + collectivityClass.getClassID() + 
				", EstablishDate = '" + collectivityClass.getEstablishDate() + "', ClassTerm = '" + 
				collectivityClass.getClassTerm() + "', ClassSort = " + collectivityClass.getClassSort() + 
				", ClassAttribute = " + collectivityClass.getClassAttribute() + ", ServiceType = " + 
				collectivityClass.getServiceType() + ", ClassName = '" + collectivityClass.getClassName() + 
				"', Assignment = " + collectivityClass.getAssignment() + ", Liability = " + collectivityClass.getLiability() + 
				", TeacherID = '" + collectivityClass.getTeacherID() + "', TeacherName = '" + collectivityClass.getTeacherName() + "' where ClassID = " + collectivityClass.getClassID() + "");
	}
	public void updateStudent(Statement statement, Student student, CollectivityClass collectivityClass) throws SQLException {
		statement.execute("update student set ClassName = (select ClassName from collectivityClass where ClassID = " + collectivityClass.getClassID() + ") where ClassID = " + collectivityClass.getClassID() + "");
	}
	
	public void deleteCollectivityClass(Statement statement, String classID) throws SQLException {
		statement.execute("delete from collectivityclass where ClassID = " + classID + "");
	}
	
	public ArrayList<CollectivityClass> getAllCollectivityClasss() throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		ArrayList<CollectivityClass> al = new ArrayList<CollectivityClass>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select ClassID,ClassName, ClassSort, TeacherName, EstablishDate from collectivityclass ORDER BY ClassID ASC");
		CollectivityClass collectivityClass = null;
		
		while (rs.next()) {
			collectivityClass = new CollectivityClass();
			collectivityClass.setClassID(rs.getInt("ClassID"));
			collectivityClass.setClassName(rs.getString("ClassName"));
			collectivityClass.setClassSort(rs.getInt("ClassSort"));
			collectivityClass.setTeacherName(rs.getString("TeacherName"));
			collectivityClass.setEstablishDate(rs.getString("EstablishDate"));
						
			al.add(collectivityClass);
		}
		rs.close();
		stmt.close();
		conn.close();
		
		return al;
	}
	
	public boolean doesExist(String ClassID) throws SQLException {
		Connection conn = DBHelper.getConnection();
		Statement stmt;
		ResultSet rs;
		
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select count(*) from collectivityclass where ClassID=" + ClassID + "");
		rs.next();
		
		boolean flag = rs.getInt(1) == 0 ? false : true;
		rs.close();
		stmt.close();
		conn.close();
		return flag;
	}
}
